from elasticsearch import Elasticsearch
from pymysql import connect
from collections import defaultdict

# 连接 Elasticsearch
es = Elasticsearch([{'host': 'localhost', 'port': 9200}])

# 连接 MySQL
mydb = connect(
    host="up01",
    user="root",
    password="123456",
    database="tfec_tags"
)
mycursor = mydb.cursor()

# 从 ES 中获取数据
def get_es_data():
    query = {
        "query": {
            "match_all": {}
        }
    }
    result = es.search(index="tags_result", body=query)
    es_data = []
    for hit in result['hits']['hits']:
        source = hit['_source']
        user_id = source.get('userId')
        tags_id = source.get('tagsId')
        if tags_id:
            tags = tags_id.split(',')
            for tag in tags:
                es_data.append((user_id, tag))
    return es_data

# 从 MySQL 中获取标签解释
def get_mysql_data():
    mycursor.execute("SELECT id, name,pid FROM tbl_basic_tag")
    mysql_data = {str(row[0]): (row[1],row[2]) for row in mycursor.fetchall()}
    return mysql_data

# 关联数据
def combine_data(es_data, mysql_data):
    combined = []
    for user_id, tag_id in es_data:
        explanation = mysql_data.get(tag_id)
        combined.append((user_id, tag_id, explanation[0],explanation[1]))
    return combined

# 主函数
def main():
    es_data = get_es_data()
    mysql_data = get_mysql_data()
    combined_data = combine_data(es_data, mysql_data)
    for user_id, tag_id, explanation,pid in combined_data:
        print(f"User ID: {user_id}, Tag ID: {tag_id}, Explanation: {explanation}")
        sql = "INSERT INTO user_tags (user_id, tag_id, tag_explanation,pid) VALUES (%s, %s, %s,%s)"
        # 执行批量插入
        mycursor.executemany(sql, ((user_id,tag_id,explanation,pid),))
    mydb.commit()
if __name__ == "__main__":
    main()